package com.ysd.lis.service.qi.impl;

import cn.hutool.core.date.DateUtil;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import com.ysd.common.Result;
import com.ysd.jdbc.JdbcConnectionManager;
import com.ysd.lis.entity.DataBaseBo;
import com.ysd.lis.entity.qi.*;
import com.ysd.lis.enums.QiDataSourceEnum;
import com.ysd.lis.mapper.qi.*;
import com.ysd.lis.service.qi.QiIndexSqlDataCollectService;
import com.ysd.lis.service.qi.QiIndexSqlDataService;
import com.ysd.util.ToolsUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>
 * 指标项目条件表 服务实现类
 * </p>
 *
 * @author PRS
 * @since 2024-07-15
 */
@Service
@Slf4j
public class QiIndexSqlDataServiceImpl extends ServiceImpl<QiIndexSqlDataMapper, QiIndexSqlData> implements QiIndexSqlDataService {

    @Autowired
    QiIndexSqlMapper qiIndexSqlMapper;
    @Autowired
    QiIndexSqlWhereMapper qiIndexSqlWhereMapper;
    @Autowired
    QiIndexSqlTableMapper qiIndexSqlTableMapper;

    @Autowired
    QiDatabaseMapper qiDatabaseMapper;
    @Autowired
    QiIndexSqlDataMapper qiIndexSqlDataMapper;
    @Autowired
    QiIndexSqlDataCollectService qiIndexSqlDataCollectService;

    @Autowired
    QiIndexSqlDataCollectMapper qiIndexSqlDataCollectMapper;

    @Override
    public Result synQiIndexSqlData(QiIndexSqlData pageParam) {
        String indexId = pageParam.getIndexId();
        String itemId = pageParam.getItemId();
        String majorId = pageParam.getMajorId();
        //根据指标ID和项目ID 查询所有的专业下的sql
        MPJLambdaWrapper<QiIndexSql> mpjLambdaWrapper = new MPJLambdaWrapper<>();
        mpjLambdaWrapper.selectAll(QiIndexSql.class)
                .eq(QiIndexSql::getIndexId, indexId)
                .eq(QiIndexSql::getItemId, itemId)
                .eq(StringUtils.isNotBlank(majorId),QiIndexSql::getMajorId,majorId)
                .eq(QiIndexSql::getDelFlag, 0);
        List<QiIndexSql> qiIndexSqlList = qiIndexSqlMapper.selectList(mpjLambdaWrapper);
        if (ToolsUtils.isNotEmpty(qiIndexSqlList)) {
            try {
                for (QiIndexSql qiIndexSql : qiIndexSqlList) {
                    QiDatabase qiDatabase = qiDatabaseMapper.selectById(qiIndexSql.getQiDatabaseId());

                    QiDataSourceEnum anEnum = QiDataSourceEnum.getEnum(qiDatabase);
                    DataBaseBo dataBaseBo = anEnum.getFunc().apply(anEnum, qiDatabase);
                    JdbcConnectionManager jdbcConnectionManager = new JdbcConnectionManager();
                    Connection connection = jdbcConnectionManager.getConnection(dataBaseBo);
                    if (ToolsUtils.isNotEmpty(connection)) {

                        //根据sqlId找指定日期的where条件：SPECIFY数据
                        MPJLambdaWrapper<QiIndexSqlWhere> mpjLambdaWhereWrapper = new MPJLambdaWrapper<>();
                        mpjLambdaWhereWrapper.selectAll(QiIndexSqlWhere.class)
                                .eq(QiIndexSqlWhere::getQiSqlId, qiIndexSql.getId())
                                .eq(QiIndexSqlWhere::getWhereType, "SPECIFY")
                                .eq(QiIndexSqlWhere::getDelFlag, 0);
                        List<QiIndexSqlWhere> qiIndexSqlSpecifyList = qiIndexSqlWhereMapper.selectList(mpjLambdaWhereWrapper);

                        MPJLambdaWrapper<QiIndexSqlTable> tableWrapper = new MPJLambdaWrapper<>();
                        tableWrapper.selectAll(QiIndexSqlTable.class)
                                .eq(QiIndexSqlTable::getQiSqlId, qiIndexSql.getId())
                                .eq(QiIndexSqlTable::getDelFlag, 0);
                        List<QiIndexSqlTable> qiIndexSqlTables = qiIndexSqlTableMapper.selectList(tableWrapper);

                        Statement statement = connection.createStatement();
                        String sqlSentence = qiIndexSql.getSqlSentence();
                        Timestamp beginTime = null;
                        Timestamp endTime = null;
                        String leftParam = null;
                        Integer zdtr = 0;

                        if (ToolsUtils.isNotEmpty(qiIndexSqlSpecifyList) ) {
                            QiIndexSqlWhere sqlWhere = qiIndexSqlSpecifyList.get(0);
                            String rightParam = sqlWhere.getRightParam();
                            if(ToolsUtils.isNotEmpty(rightParam)){
                                zdtr = Integer.valueOf(rightParam);
                                if(CollectionUtils.isNotEmpty(qiIndexSqlTables)){
                                    leftParam = sqlWhere.getLeftParam();
                                    String asName ="";
                                    String tableName ="";
                                    for (QiIndexSqlTable qiIndexSqlTable : qiIndexSqlTables) {
                                        if (leftParam.contains(qiIndexSqlTable.getTableName())){
                                            asName = qiIndexSqlTable.getAlias();
                                            tableName = qiIndexSqlTable.getTableName();
                                        }
                                    }
                                    if (StringUtils.isNotBlank(asName)){
                                        leftParam = leftParam.replace(tableName,asName);
                                    }

                                }
                            }


                        }

                        //如果是true则是走的定时任务，需要查看指定日期的数据，如果有则执行指定日期，如果没有则走全量数据
                        Boolean isJob = pageParam.getIsJob();
                        if(ToolsUtils.isNotEmpty(isJob)&&isJob){
                            Calendar calendar = Calendar.getInstance(); // 获取当前日期的Calendar实例
                            calendar.add(Calendar.DATE, -zdtr); // 将日期回退一天
                            Date today = calendar.getTime();
                            beginTime = DateUtil.beginOfDay(today).toTimestamp();
                            endTime = DateUtil.endOfDay(today).toTimestamp();

                        }else {
                            if (StringUtils.isNotBlank(pageParam.getExecuteTime())) {
                                beginTime = Timestamp.valueOf(pageParam.getExecuteTime() + " 00:00:00");
                                endTime = Timestamp.valueOf(pageParam.getExecuteTime() + " 23:59:59");
                            }else {
                                Calendar calendar = Calendar.getInstance(); // 获取当前日期的Calendar实例
                                calendar.add(Calendar.DATE, 0); // 将日期回退一天
                                Date today = calendar.getTime();
                                beginTime = DateUtil.beginOfDay(today).toTimestamp();
                                endTime = DateUtil.endOfDay(today).toTimestamp();
                            }
                        }

                        //删除历史数据
                        LambdaUpdateWrapper<QiIndexSqlData> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();
                        lambdaUpdateWrapper.eq(QiIndexSqlData::getIndexId, qiIndexSql.getIndexId())
                                .eq(QiIndexSqlData::getItemId, qiIndexSql.getItemId())
                                .eq(QiIndexSqlData::getMajorId, qiIndexSql.getMajorId());
                        if (ToolsUtils.isNotEmpty(leftParam)&&ToolsUtils.isNotEmpty(beginTime) && ToolsUtils.isNotEmpty(endTime)) {
                            lambdaUpdateWrapper.ge(QiIndexSqlData::getOperateDate, beginTime)
                                    .le(QiIndexSqlData::getOperateDate, endTime);
                        }
                        qiIndexSqlDataMapper.delete(lambdaUpdateWrapper);

                        //删除历史数据汇总
                        LambdaUpdateWrapper<QiIndexSqlDataCollect> deleteWrapper = new LambdaUpdateWrapper<>();
                        deleteWrapper.eq(QiIndexSqlDataCollect::getIndexId, qiIndexSql.getIndexId())
                                .eq(QiIndexSqlDataCollect::getItemId, qiIndexSql.getItemId())
                                .eq(QiIndexSqlDataCollect::getMajorId, qiIndexSql.getMajorId());
                        if (ToolsUtils.isNotEmpty(leftParam)&&ToolsUtils.isNotEmpty(beginTime) && ToolsUtils.isNotEmpty(endTime)) {
                            deleteWrapper.ge(QiIndexSqlDataCollect::getOperateDate, beginTime)
                                    .le(QiIndexSqlDataCollect::getOperateDate, endTime);
                        }
                        qiIndexSqlDataCollectMapper.delete(deleteWrapper);

                        if (ToolsUtils.isNotEmpty(leftParam)&&ToolsUtils.isNotEmpty(beginTime) && ToolsUtils.isNotEmpty(endTime)) {
                            sqlSentence += " and " + leftParam + " >= '" + beginTime + "' and " + leftParam + " <= '" + endTime + "'";
                        }
                        try {

                            ResultSet resultSet = statement.executeQuery(sqlSentence);
                            List<QiIndexSqlData> dataList = new ArrayList<>();
                            // 处理查询结果
                            while (resultSet.next()) {
                                QiIndexSqlData qiIndexSqlData = new QiIndexSqlData();
                                qiIndexSqlData.setIndexId(qiIndexSql.getIndexId());
                                qiIndexSqlData.setIndexCode(qiIndexSql.getIndexCode());
                                qiIndexSqlData.setItemId(qiIndexSql.getItemId());
                                qiIndexSqlData.setItemCode(qiIndexSql.getItemCode());
                                qiIndexSqlData.setMajorId(qiIndexSql.getMajorId());
                                qiIndexSqlData.setMajorCode(qiIndexSql.getMajorCode());
                                try {
                                    String dataNo = resultSet.getString("dataNo");
                                    qiIndexSqlData.setDataNo(dataNo);

                                }catch (Exception e){
                                    e.printStackTrace();
                                }
                                try {
                                    Integer quantity = resultSet.getInt("quantity");
                                    qiIndexSqlData.setQuantity(quantity);
                                }catch (Exception e){
                                    e.printStackTrace();
                                }
                                try {
                                    Timestamp operateDate = resultSet.getTimestamp("operateDate");
                                    qiIndexSqlData.setOperateDate(operateDate);
                                }catch (Exception e){
                                    e.printStackTrace();
                                }
                                String dataSource = "LIS";
                                qiIndexSqlData.setDataSource(dataSource);
                                qiIndexSqlData.setOrgId(qiIndexSql.getOrgId());
                                dataList.add(qiIndexSqlData);
                            }

                            this.saveBatch(dataList);
                            //将明细数据按照日期汇总到汇总表中
                            if(ToolsUtils.isNotEmpty(dataList)){
                                for(QiIndexSqlData sqlData : dataList){
                                    Timestamp operateDate = sqlData.getOperateDate();
                                    if(ToolsUtils.isNotEmpty(operateDate)){
                                        Date date = new Date(operateDate.getTime());
                                        beginTime = DateUtil.beginOfDay(date).toTimestamp();
                                        sqlData.setCollectDate(beginTime);
                                    }
                                }
                                //按照日期分组
                                Map<Timestamp, List<QiIndexSqlData>> sqlDataGroupMap = dataList.stream().filter(a-> ToolsUtils.isNotEmpty(a.getCollectDate())).collect(Collectors.groupingBy(QiIndexSqlData::getCollectDate));

                                List<QiIndexSqlDataCollect> dataCollectList = new ArrayList<>();
                                for(Timestamp timestamp : sqlDataGroupMap.keySet()){
                                    List<QiIndexSqlData> dataList1 = sqlDataGroupMap.get(timestamp);

                                    QiIndexSqlDataCollect qiIndexSqlDataCollect = new QiIndexSqlDataCollect();
                                    qiIndexSqlDataCollect.setIndexId(qiIndexSql.getIndexId());
                                    qiIndexSqlDataCollect.setIndexCode(qiIndexSql.getIndexCode());
                                    qiIndexSqlDataCollect.setItemId(qiIndexSql.getItemId());
                                    qiIndexSqlDataCollect.setItemCode(qiIndexSql.getItemCode());
                                    qiIndexSqlDataCollect.setMajorId(qiIndexSql.getMajorId());
                                    qiIndexSqlDataCollect.setMajorCode(qiIndexSql.getMajorCode());
                                    qiIndexSqlDataCollect.setOperateDate(timestamp);
                                    int sum = dataList1.stream().mapToInt(QiIndexSqlData::getQuantity).sum();
                                    qiIndexSqlDataCollect.setQuantity(sum);

                                    qiIndexSqlDataCollect.setOrgId(qiIndexSql.getOrgId());
                                    dataCollectList.add(qiIndexSqlDataCollect);
                                }
                                qiIndexSqlDataCollectService.saveBatch(dataCollectList);
                            }
                            // 关闭连接和Statement对象
                            resultSet.close();
                        } catch (Exception e) {
                            log.error(e.getMessage());
                            continue;
                        }

                        statement.close();
                        connection.close();
                        jdbcConnectionManager.closeConnection(connection);
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return Result.succ("执行成功");
    }
}
